package project.model;

import java.io.Serializable;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import project.dao.EmployeeDAO;

import model.pojo.Employee;
import models.util.MySqlDataAccessHelper;
import models.util.UtilInitLang;

public class PrProject implements Serializable {

	private static final long serialVersionUID = 4565952807602103623L;

	private String idProject;
	private String nameProject;
	private String description;
	private String idManager;
	private Date dateStart = new Date();
	private Date dateComplete = new Date();
	private int status;
	private String manager;
	private String status2;
	private Employee em=new Employee();
	private String db=null;
	private ArrayList<PrComposition> listCoder = new ArrayList<PrComposition>();
	private ArrayList<PrComposition> listTester = new ArrayList<PrComposition>();
	
	private String[] viewListcoder=null;
	private String[] viewListtester=null;
	private String idErr="";
	private String idEm="";
	private String idCom="";
	private String idLog="";
	
	private ArrayList<PrTestProject> listErr = new ArrayList<PrTestProject>();

	private String dS="";
	private String dC="";

	private String nameManager;
	
	
	
	public String getIdEm(String dbc) {
		String s="";
		if(dbc!=null){
			MySqlDataAccessHelper mysql =new MySqlDataAccessHelper();
			try{
				mysql.open(dbc);
				String sql="select DISTINCT pr_composition.IDEmployee from pr_project,pr_composition where pr_project.IDProject=pr_composition.IDProject and pr_project.IDProject='"+this.idProject+"'";
				ResultSet rs= mysql.executeQuery(sql);
				while(rs.next()){
					if(s.equals("")){
						s="'"+rs.getString("IDEmployee")+"'";
					}else{
						s+=",'"+rs.getString("IDEmployee")+"'";
					}
				}
				if(s.equals("")==false){
					idEm=s;
				}
				rs.close();
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				mysql.close();
			}
		}
		return idEm;
	}

	public void setIdEm(String idEm) {
		this.idEm = idEm;
	}

	
	
	public String getIdCom(String dbc) {
		String s="";
		if(dbc!=null){
			MySqlDataAccessHelper mysql =new MySqlDataAccessHelper();
			try{
				mysql.open(dbc);
				String sql="SELECT pr_comment.IDComment from pr_comment,pr_testproject,pr_project where pr_comment.IDTestProject=pr_testproject.IDTest and pr_testproject.IDProject=pr_project.IDProject and pr_project.IDProject='"+this.idProject+"'";
				ResultSet rs= mysql.executeQuery(sql);
				while(rs.next()){
					if(s.equals("")){
						s="'"+rs.getString("IDComment")+"'";
					}else{
						s+=",'"+rs.getString("IDComment")+"'";
					}
				}
				if(s.equals("")==false){
					idCom=s;
				}
				rs.close();
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				mysql.close();
			}
		}
		return idCom;
	}

	public void setIdCom(String idCom) {
		this.idCom = idCom;
	}

	
	
	public String getIdLog(String dbc) {
		String s="";
		if(dbc!=null){
			MySqlDataAccessHelper mysql =new MySqlDataAccessHelper();
			try{
				mysql.open(dbc);
				String sql="SELECT pr_logchange.IDLogChange from pr_testproject,pr_project,pr_logchange where pr_logchange.IDTest=pr_testproject.IDTest and pr_testproject.IDProject=pr_project.IDProject and pr_project.IDProject='"+this.idProject+"'";
				ResultSet rs= mysql.executeQuery(sql);
				while(rs.next()){
					if(s.equals("")){
						s="'"+rs.getString("")+"'";
					}else{
						s=",'"+rs.getString("")+"'";
					}
				}
				if(s.equals("")==false){
					idLog=s;
				}
				rs.close();
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				mysql.close();
			}
		}
		return idLog;
	}

	public void setIdLog(String idLog) {
		this.idLog = idLog;
	}

	public ArrayList<PrTestProject> getListErr(String dbc) {
		if(dbc!=null){
			MySqlDataAccessHelper mysql =new MySqlDataAccessHelper();
			try{
				mysql.open(dbc);
				String sql="select pr_testproject.IDProject,pr_testproject.IDTest from pr_testproject where pr_testproject.IDProject='"+this.idProject+"'";
				ResultSet rs= mysql.executeQuery(sql);
				PrTestProject er;
				while(rs.next()){
					er= new PrTestProject();
					er.setDb(dbc);
					er.setIdProject(rs.getString("IDProject"));
					er.setIdTest(rs.getString("IDTest"));
					listErr.add(er);
				}
				rs.next();
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				mysql.close();
			}
		}
		return listErr;
	}

	public void setListErr(ArrayList<PrTestProject> listErr) {
		this.listErr = listErr;
	}

	public String getdS() {
		return dS;
	}

	public void setdS(String dS) {
		this.dS = dS;
	}

	public String getdC() {
		return dC;
	}

	public void setdC(String dC) {
		this.dC = dC;
	}

	public String[] getViewListcoder(String dbc) {
		if(dbc!=null){
			ArrayList<PrComposition> lst=this.getListCoder(dbc);
			if(lst!=null && lst.size()>0){
				viewListcoder = new String[lst.size()];
				for(int i=0;i<lst.size();i++){
					viewListcoder[i]=lst.get(i).getEminfo(dbc).getFullName()+lst.get(i).getStatusName();
				}
			}
		}
		return viewListcoder;
	}

	
	public String getIdErr(String dbc) {
		String s="";
		if(dbc!=null){
			MySqlDataAccessHelper mysql =new MySqlDataAccessHelper();
			try{
				mysql.open(dbc);
				String sql="select pr_testproject.IDTest from pr_testproject where pr_testproject.IDProject='"+this.idProject+"'";
				ResultSet rs= mysql.executeQuery(sql);
				System.out.println(sql);
				while(rs.next()){
					
					if(s.equals("")){
						s="'"+rs.getString("IDTest")+"'";
					}else{
						s+=","+"'"+rs.getString("IDTest")+"'";
					}
				}
				
				if(s.equals("")==false){
					idErr=s;
				}
				
				rs.next();
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				mysql.close();
			}
		}
		return idErr;
	}

	public void setIdErr(String idErr) {
		this.idErr = idErr;
	}

	public void setViewListcoder(String[] viewListcoder) {
		this.viewListcoder = viewListcoder;
	}

	public String[] getViewListtester(String dbc) {
		if(dbc!=null){
			ArrayList<PrComposition> lst=this.getListTester(dbc);
			if(lst!=null && lst.size()>0){
				viewListtester = new String[lst.size()];
				for(int i=0;i<lst.size();i++){
					viewListtester[i]=lst.get(i).getEminfo(dbc).getFullName()+lst.get(i).getStatusName();
				}
			}
		}
		return viewListtester;
	}

	public void setViewListtester(String[] viewListtester) {
		this.viewListtester = viewListtester;
	}

	public String getDb() {
		return db;
	}

	public void setDb(String db) {
		this.db = db;
	}
	
	

	public ArrayList<PrComposition> getListCoder(String dbc) {
		if(dbc!=null){
			MySqlDataAccessHelper mysql =new MySqlDataAccessHelper();
			try{
				mysql.open(dbc);
				String sql="select * from pr_composition where pr_composition.IDProject='"+idProject+"' and pr_composition.Position=1";
				ResultSet rs=mysql.executeQuery(sql);
				PrComposition pc;
				while(rs.next()){
					pc=new PrComposition();
					pc.setIdComposition(rs.getString("IDComposition"));
					pc.setIdProject(rs.getString("IDProject"));
					pc.setIdEmployee(rs.getString("IDEmployee"));
					pc.setPosition(rs.getInt("Position"));
					pc.setStatus(rs.getInt("Status"));
					if(rs.getInt("Status")==1){
						pc.setStatusName(" (Not working)");
					}
					
					
					listCoder.add(pc);
				}
				rs.close();
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				mysql.close();
			}
		}
		return listCoder;
	}

	public void setListCoder(ArrayList<PrComposition> listCoder) {
		this.listCoder = listCoder;
	}

	public ArrayList<PrComposition> getListTester(String dbc) {
		if(dbc!=null){
			MySqlDataAccessHelper mysql =new MySqlDataAccessHelper();
			try{
				mysql.open(dbc);
				String sql="select * from pr_composition where pr_composition.IDProject='"+idProject+"' and pr_composition.Position=0";
				System.out.println("sql: "+sql);
				ResultSet rs=mysql.executeQuery(sql);
				PrComposition pc;
				while(rs.next()){
					pc=new PrComposition();
					pc.setIdComposition(rs.getString("IDComposition"));
					pc.setIdProject(rs.getString("IDProject"));
					pc.setIdEmployee(rs.getString("IDEmployee"));
					pc.setPosition(rs.getInt("Position"));
					pc.setStatus(rs.getInt("Status"));
					if(rs.getInt("Status")==1){
						pc.setStatusName(" (Not working)");
					}
					listTester.add(pc);
				}
				rs.close();
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				mysql.close();
			}
		}
		return listTester;
	}

	public void setListTester(ArrayList<PrComposition> listTester) {
		this.listTester = listTester;
	}

	public PrProject(String idProject, String nameProject, String description,
			String idManager, String manager, Date dateComplete, int status,
			Date dateStart) {
		this.idProject = idProject;
		this.nameProject = nameProject;
		this.description = description;
		this.idManager = idManager;
		this.manager = manager;
		this.dateStart = dateStart;
		this.dateComplete = dateComplete;
		this.status = status;
	}

	public PrProject(String idProject, String nameProject, String description,String manager,
			Date dateComplete, int status) {
		this.idProject = idProject;
		this.nameProject = nameProject;
		this.description = description;
		this.manager=manager;
		this.dateComplete = dateComplete;
		this.status = status;
	}

	public PrProject() {
		// TODO Auto-generated constructor stub
	}

	public String getIdProject() {
		return idProject;
	}

	public void setIdProject(String idProject) {
		this.idProject = idProject;
	}

	public String getNameProject() {
		return nameProject;
	}

	public void setNameProject(String nameProject) {
		this.nameProject = nameProject;
	}

	public String getDescription() {
		return description;
	}

	public void setDescription(String description) {
		this.description = description;
	}

	public String getIdManager() {
		return idManager;
	}

	public void setIdManager(String idManager) {
		this.idManager = idManager;
	}

	public Date getDateComplete() {
		return dateComplete;
	}

	public void setDateComplete(Date dateComplete) {
		this.dateComplete = dateComplete;
	}

	public int getStatus() {
		return status;
	}

	public void setStatus(int status) {
		this.status = status;
	}

	public void setStatus2(String status2) {
		this.status2 = status2;
	}

	public String getStatus2() {
		 UtilInitLang lang = new UtilInitLang();
		 if(status == 0){
		 status2 = lang.getValue("notcomplete");
		 } else {
		 status2 = lang.getValue("complete");
		 }
		return status2;
	}

	public void setManager(String manager) {

		this.manager = manager;
	}

	public String getManager() {
		if(db!=null){System.out.println("1111111111111111111111111111");
			EmployeeDAO emdao = new EmployeeDAO();
			manager = emdao.getNameEmployee(idManager,db);
		}
		
		return manager;
	}

	public void setDateStart(Date dateStart) {
		this.dateStart = dateStart;
	}

	public Date getDateStart() {
		return dateStart;
	}
	
	
	public Employee getEm() throws SQLException {
		if(db!=null){
			if(idManager !=null){
				EmployeeDAO emdao=new EmployeeDAO();
				em = emdao.getOneEmployee(idManager,db);
			}	
		}			
		return em;
	}

	public void setEm(Employee em) {
		this.em = em;
	}

	@Override
	public String toString() {
		// TODO Auto-generated method stub
		return this.nameProject;
	}
	
	
	public ArrayList<Employee> getListCoderEm(String dbc) {
		ArrayList<Employee> el = new ArrayList<Employee>();
		if(dbc!=null){
			MySqlDataAccessHelper mysql =new MySqlDataAccessHelper();
			try{
				mysql.open(dbc);
				String sql="select pr_composition.IDEmployee,pr_composition.Status,employee.FullName " +
						"from pr_composition,employee " +
						"where pr_composition.IDEmployee=employee.IDEmployee " +
						"and pr_composition.IDProject='"+idProject+"' " +
								"and pr_composition.Position=1";
				ResultSet rs=mysql.executeQuery(sql);
				//PrComposition pc;
				Employee ee;
				while(rs.next()){
					ee=new Employee();
					ee.setIdemployee(rs.getString("IDEmployee"));
					if(rs.getInt("Status")==1){
						ee.setFullName(rs.getString("FullName")+" ( Not working )");
					}else{
						ee.setFullName(rs.getString("FullName"));
					}						
					el.add(ee);
				}
				rs.close();
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				mysql.close();
			}
		}
		return el;
	}
	
	public ArrayList<Employee> getListTesterEm(String dbc) {
		ArrayList<Employee> el = new ArrayList<Employee>();
		if(dbc!=null){
			MySqlDataAccessHelper mysql =new MySqlDataAccessHelper();
			try{
				mysql.open(dbc);
				String sql="select pr_composition.IDEmployee,pr_composition.Status,employee.FullName " +
						"from pr_composition,employee " +
						"where pr_composition.IDEmployee=employee.IDEmployee " +
						"and pr_composition.IDProject='"+idProject+"' " +
								"and pr_composition.Position=0";
				System.out.println("sql: "+sql);
				ResultSet rs=mysql.executeQuery(sql);
				//PrComposition pc;
				Employee ee;
				while(rs.next()){
					ee=new Employee();
					//pc=new PrComposition();
					//pc.setIdComposition(rs.getString("IDComposition"));
					//pc.setIdProject(rs.getString("IDProject"));
					//pc.setIdEmployee(rs.getString("IDEmployee"));
					//pc.setPosition(rs.getInt("Position"));
//					pc.setStatus(rs.getInt("Status"));
					ee.setIdemployee(rs.getString("IDEmployee"));
					if(rs.getInt("Status")==1){
						ee.setFullName(rs.getString("FullName")+" ( Not working )");
					}else{
						ee.setFullName(rs.getString("FullName"));
					}
									
					
					el.add(ee);
				}
				rs.close();
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				mysql.close();
			}
		}
		return el;
	}

	public void setNameManager(String nameManager) {
		this.nameManager = nameManager;
	}

	public String getNameManager() {
		return nameManager;
	}
}
